package org.example.src.repository;

import org.example.src.entity.WeeklyReport;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.util.List;
import java.util.Optional;

@Repository
public interface WeeklyReportRepository extends JpaRepository<WeeklyReport, Long> {
    @Query("SELECT w FROM WeeklyReport w WHERE w.student.id = :studentId")
    List<WeeklyReport> findByStudentId(@Param("studentId") Long studentId);

    @Query("SELECT w FROM WeeklyReport w WHERE w.application.id = :applicationId")
    List<WeeklyReport> findByApplicationId(@Param("applicationId") Long applicationId);

    @Query("SELECT w FROM WeeklyReport w WHERE w.weekNumber = :weekNumber")
    List<WeeklyReport> findByWeekNumber(@Param("weekNumber") Integer weekNumber);

    @Query("SELECT w FROM WeeklyReport w WHERE w.submissionDate BETWEEN :startDate AND :endDate")
    List<WeeklyReport> findBySubmissionDateBetween(
            @Param("startDate") LocalDate startDate,
            @Param("endDate") LocalDate endDate
    );

    @Query("SELECT w FROM WeeklyReport w WHERE w.student.id = :studentId AND w.weekNumber = :weekNumber")
    Optional<WeeklyReport> findByStudentAndWeek(
            @Param("studentId") Long studentId,
            @Param("weekNumber") Integer weekNumber
    );

    @Query("SELECT COUNT(w) FROM WeeklyReport w WHERE w.student.id = :studentId")
    Long countByStudentId(@Param("studentId") Long studentId);

    @Query("SELECT AVG(w.score) FROM WeeklyReport w WHERE w.student.id = :studentId AND w.score IS NOT NULL")
    Double findAverageScoreByStudentId(@Param("studentId") Long studentId);
}